CREATE OR REPLACE PACKAGE typlm.auto_serial_pkg AS
    -- 获取下一个编码函数
    FUNCTION get_next_code(p_category_code VARCHAR2) RETURN VARCHAR2;
    -- 释放编码过程
    PROCEDURE release_code(p_full_code VARCHAR2);
    -- 获取分类统计信息函数
    FUNCTION get_category_stats(p_category_code VARCHAR2) RETURN VARCHAR2;
    -- 清理无效编码过程（新增）
    PROCEDURE cleanup_invalid_codes;
END auto_serial_pkg;
/

CREATE OR REPLACE PACKAGE BODY typlm.auto_serial_pkg AS
    -- 有效字符集（1-9, A-Z，排除I,O,Q）
    g_valid_chars VARCHAR2(32) := '123456789ABCDEFGHJKLMNPRSTUVWXYZ';

    -- 数值转编码部分
    FUNCTION number_to_serial(p_num NUMBER) RETURN VARCHAR2 IS
        v_first_pos NUMBER;
        v_second_pos NUMBER;
BEGIN
        IF p_num < 1 THEN
            RETURN '01'; -- 最小编码
END IF;

        -- 转换为0-based
        v_second_pos := MOD(p_num-1, 31); -- 31是可用的第二位字符数
        v_first_pos := FLOOR((p_num-1) / 31);

        -- 确保第一位是1-9
        IF v_first_pos = 0 THEN
            RETURN '0' || SUBSTR(g_valid_chars, v_second_pos+1, 1);
ELSE
            RETURN SUBSTR(g_valid_chars, v_first_pos, 1) ||
                   SUBSTR(g_valid_chars, v_second_pos+1, 1);
END IF;
END;

    -- 获取下一个编码函数
    FUNCTION get_next_code(p_category_code VARCHAR2) RETURN VARCHAR2 IS
        PRAGMA AUTONOMOUS_TRANSACTION;
        v_next_num NUMBER := 0;
        v_sequence_part VARCHAR2(10);
        v_full_code VARCHAR2(20);
        v_category_code VARCHAR2(1);
        v_min_available_num NUMBER;
BEGIN
        DBMS_OUTPUT.PUT_LINE('开始处理分类码: ' || p_category_code);

        -- 第一阶段：导入缺失的历史数据
FOR hist_rec IN (
            SELECT PARTNUMBER
            FROM ty_partmaster
            WHERE REGEXP_LIKE(PARTNUMBER, '^'||p_category_code||'[0-9A-Z]{2}$')
              AND NOT EXISTS (
                SELECT 1 FROM auto_material_codes
                WHERE full_code = PARTNUMBER
            )
            ORDER BY PARTNUMBER
            ) LOOP
BEGIN
                    DBMS_OUTPUT.PUT_LINE('发现历史编码: ' || hist_rec.PARTNUMBER);

                    v_category_code := SUBSTR(hist_rec.PARTNUMBER, 1, 1);
                    v_sequence_part := SUBSTR(hist_rec.PARTNUMBER, 2, 2);

                    -- 计算序列号数值
BEGIN
                        v_next_num := TO_NUMBER(v_sequence_part);
EXCEPTION
                        WHEN OTHERS THEN
                            v_next_num :=
                                    (INSTR(g_valid_chars, SUBSTR(v_sequence_part, 1, 1)) - 1) * 31 +
                                    (INSTR(g_valid_chars, SUBSTR(v_sequence_part, 2, 1)) - 1) + 1;
END;

INSERT INTO auto_material_codes(
    full_code, category_code,
    sequence_number, sequence_part,
    status, created_time, last_updated
)
VALUES(
          hist_rec.PARTNUMBER, v_category_code,
          v_next_num, v_sequence_part,
          'USED', SYSTIMESTAMP, SYSTIMESTAMP
      );

COMMIT;
DBMS_OUTPUT.PUT_LINE('已导入历史编码: ' || hist_rec.PARTNUMBER);
EXCEPTION
                    WHEN OTHERS THEN
                        ROLLBACK;
                        DBMS_OUTPUT.PUT_LINE('导入失败: ' || hist_rec.PARTNUMBER || ' - ' || SQLERRM);
END;
END LOOP;

        -- 第二阶段：优先使用可回收的编码
BEGIN
            -- 1. 首先尝试获取最小的可回收编码
BEGIN
SELECT MIN(sequence_number) INTO v_min_available_num
FROM auto_material_codes
WHERE category_code = p_category_code
  AND status = 'AVAILABLE';

IF v_min_available_num IS NOT NULL THEN
                    -- 获取完整的可用编码信息
BEGIN
SELECT full_code, sequence_part INTO v_full_code, v_sequence_part
FROM auto_material_codes
WHERE category_code = p_category_code
  AND sequence_number = v_min_available_num
  AND status = 'AVAILABLE'
  AND ROWNUM = 1
    FOR UPDATE;

-- 检查该编码是否已被使用
SELECT COUNT(*) INTO v_next_num
FROM auto_material_codes
WHERE full_code = v_full_code
  AND status = 'USED';

IF v_next_num = 0 THEN
                            -- 标记为已使用
UPDATE auto_material_codes
SET status = 'USED',
    sequence_part = v_sequence_part,
    last_updated = SYSTIMESTAMP
WHERE full_code = v_full_code;

COMMIT;
RETURN v_full_code;
END IF;
EXCEPTION
                        WHEN NO_DATA_FOUND THEN
                            NULL;
END;
END IF;
EXCEPTION
                WHEN NO_DATA_FOUND THEN
                    NULL;
END;

            -- 2. 生成新的序列号
BEGIN
                -- 获取当前最大序列号
SELECT NVL(MAX(sequence_number), 0) INTO v_next_num
FROM auto_material_codes
WHERE category_code = p_category_code;

<<generate_new_code>>
                LOOP
                    v_next_num := v_next_num + 1;
                    v_sequence_part := number_to_serial(v_next_num);
                    v_full_code := p_category_code || v_sequence_part;

                    -- 检查编码是否已存在
BEGIN
INSERT INTO auto_material_codes(
    full_code, category_code,
    sequence_number, sequence_part,
    status
)
VALUES(
          v_full_code, p_category_code,
          v_next_num, v_sequence_part,
          'USED'
      );
COMMIT;
RETURN v_full_code;
EXCEPTION
                        WHEN DUP_VAL_ON_INDEX THEN
                            DBMS_OUTPUT.PUT_LINE('编码已存在: ' || v_full_code || ', 尝试下一个');
CONTINUE;
END;
END LOOP;
END;
END;
END;

    -- 释放编码过程
    PROCEDURE release_code(p_full_code VARCHAR2) IS
        PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
UPDATE auto_material_codes
SET status = 'AVAILABLE',
    material_id = NULL,
    last_updated = SYSTIMESTAMP
WHERE full_code = p_full_code;

IF SQL%ROWCOUNT = 0 THEN
            RAISE_APPLICATION_ERROR(-20002, '编码不存在: ' || p_full_code);
END IF;

COMMIT;
END;

    -- 获取分类统计信息函数
    FUNCTION get_category_stats(p_category_code VARCHAR2) RETURN VARCHAR2 IS
        v_used NUMBER := 0;
        v_available NUMBER := 0;
        v_last_code VARCHAR2(20);
        v_result VARCHAR2(200);
BEGIN
SELECT COUNT(*) INTO v_used
FROM auto_material_codes
WHERE category_code = p_category_code
  AND status = 'USED';

SELECT COUNT(*) INTO v_available
FROM auto_material_codes
WHERE category_code = p_category_code
  AND status = 'AVAILABLE';

BEGIN
SELECT MAX(full_code) INTO v_last_code
FROM auto_material_codes
WHERE category_code = p_category_code;
EXCEPTION
            WHEN NO_DATA_FOUND THEN
                v_last_code := NULL;
END;

        v_result := '分类码: ' || p_category_code ||
                    ' | 已使用: ' || v_used ||
                    ' | 可回收: ' || v_available ||
                    ' | 最后编码: ' || NVL(v_last_code, '无');
RETURN v_result;
END;

    -- 清理无效编码过程
    PROCEDURE cleanup_invalid_codes IS
        PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
DELETE FROM auto_material_codes
WHERE status = 'USED'
  AND NOT EXISTS (
    SELECT 1 FROM ty_partmaster
    WHERE PARTNUMBER = full_code
);

DBMS_OUTPUT.PUT_LINE('已清理 ' || SQL%ROWCOUNT || ' 条无效记录');
COMMIT;
END;
END auto_serial_pkg;
/




-- 生成所有A-Z分类编码
BEGIN
    -- 清理无效数据
    typlm.auto_serial_pkg.cleanup_invalid_codes;

    -- 为每个字母分类生成一个编码
FOR c IN ASCII('A')..ASCII('Z') LOOP
BEGIN
                DBMS_OUTPUT.PUT_LINE('分类 ' || CHR(c) || ' 生成编码: ' ||
                                     typlm.auto_serial_pkg.get_next_code(CHR(c)));
EXCEPTION
                WHEN OTHERS THEN
                    DBMS_OUTPUT.PUT_LINE('分类 ' || CHR(c) || ' 错误: ' || SQLERRM);
END;
END LOOP;

    -- 清理无效数据
    typlm.auto_serial_pkg.cleanup_invalid_codes;
END;
/



-- 查找auto_material_codes中存在但ty_partmaster中不存在的记录
SELECT full_code, created_time
FROM auto_material_codes
WHERE status = 'USED'
  AND NOT EXISTS (
    SELECT 1 FROM ty_partmaster
    WHERE PARTNUMBER = full_code
)
ORDER BY created_time DESC;


-- 查找ty_partmaster中存在但auto_material_codes中不存在的记录
SELECT PARTNUMBER
FROM ty_partmaster
WHERE REGEXP_LIKE(PARTNUMBER, '^[A-Z][0-9A-Z]{2}$')
  AND NOT EXISTS (
    SELECT 1 FROM auto_material_codes
    WHERE full_code = PARTNUMBER
)
ORDER BY PARTNUMBER;
